﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Configuration;
using System.Reflection;
using System.Data.SqlClient;
using System.IO;
using System.Text.RegularExpressions;
using System.Diagnostics;
using System.Dynamic;

namespace TData
{
    public class TDataBase
    {
        public int CommandTimeout
        {
            get { return Command.CommandTimeout; }
            set { Command.CommandTimeout = value; }
        }

        private DbProviderFactory _factory;
        public DbProviderFactory Factory
        {
            get { return _factory; }
        }

        private DbConnection _connection;
        public DbConnection Connection
        {
            get { return _connection; }
        }

        private DbCommand _command;
        public DbCommand Command
        {
            get { return _command; }
        }

        private string _dbProviderName;
        public string DbProviderName
        {
            get { return _dbProviderName; }
        }

        /// <summary>
        /// Create TDataBase from connectionStrings section in App.config or Web.config
        /// </summary>
        /// <param name="connectionStringName">name of connectionString</param>
        public TDataBase(string connectionStringName)
        {
            // Get the collection of connection strings.
            ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;

            if (settings == null) throw new TDataBaseException("No ConnectionStringSettings in application configuration");

            try
            {
                foreach (ConnectionStringSettings cs in settings)
                {
                    if (cs.Name == connectionStringName)
                    {
                        _dbProviderName = cs.ProviderName;
                        _factory = DbProviderFactories.GetFactory(_dbProviderName);
                        _connection = Factory.CreateConnection();
                        Connection.ConnectionString = cs.ConnectionString;
                        _command = Factory.CreateCommand();
                        Command.Connection = Connection;
                        break;
                    }
                }

                if (Connection == null)
                    throw new TDataBaseException(string.Format("Cannot find connectionString name: [{0}] in application configuration", connectionStringName));
            }
            catch (System.ArgumentException ex)
            {
                throw new TDataBaseException(string.Format("connectionString: [{0}] is not vaild", connectionStringName), ex);
            }
        }

        #region Query

        public DataTable ExecuteDataTable(string sql, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            DataTable dtt = new DataTable();
            try
            {
                Command.CommandType = commandType;
                Command.CommandText = sql;
                if (paramObj != null) CreateDbParameters(paramObj);

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                var daa = Factory.CreateDataAdapter();
                daa.SelectCommand = Command;

                dtt = new DataTable();
                daa.Fill(dtt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }

            return dtt;
        }

        public int ExecuteNonQuery(string sql, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            int result = 0;
            try
            {
                Command.CommandType = commandType;
                Command.CommandText = sql;
                if (paramObj != null) CreateDbParameters(paramObj);

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                result = Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }

            return result;
        }

        public object ExecuteScalar(string sql, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            object result;
            try
            {
                Command.CommandType = commandType;
                Command.CommandText = sql;
                if (paramObj != null) CreateDbParameters(paramObj);

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
                result = Command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }

            return result;
        }

        public T ExecuteScalar<T>(string sql, object paramObj = null)
        {
            try
            {
                Command.CommandText = sql;
                if (paramObj != null) CreateDbParameters(paramObj);

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
                object value = Command.ExecuteScalar();

                if (value != null && value != DBNull.Value)
                    return (T)Convert.ChangeType(value, typeof(T));
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }

            return default(T);
        }

        public void ExecuteReader(string sql, Action<IDataReader> block, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            try
            {
                Command.CommandType = commandType;
                Command.CommandText = sql;
                if (paramObj != null) CreateDbParameters(paramObj);

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                using (IDataReader reader = Command.ExecuteReader())
                {
                    block(reader);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }
        }

        public List<T> ExecuteList<T>(string sql, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            try
            {
                Type mapType = typeof(T);
                List<T> mapList = new List<T>();

                Command.CommandType = commandType;
                Command.CommandText = sql;

                /*Get the current dataread columns*/
                List<string> columnNamesInQuery = new List<string>();
                foreach(DataRow dr in reader.GetSchemaTable().Rows) columnNamesInQuery.Add(Convert.ToString(dr[0]));

                ExecuteReader(sql, (reader) =>
                {
                    while (reader.Read())
                    {
                        T mapInstance;
                        if (mapType == typeof(string) || mapType.IsValueType == true)
                        {
                            mapInstance = (reader[0] is System.DBNull) ? default(T) : (T)reader[0];
                        }
                        else
                        {
                            mapInstance = (T)Activator.CreateInstance(mapType);
                            foreach (PropertyInfo prop in mapType.GetProperties())
                            {
                                object columnValue = null;

                                var colAttrs = prop.GetCustomAttributes(typeof(TColumnMapAttribute), false);
                                TColumnMapAttribute colMapAttr = new TColumnMapAttribute();
                                if (colAttrs.Length > 0) colMapAttr = colAttrs[0] as TColumnMapAttribute;

                                string colName = (string.IsNullOrWhiteSpace(colMapAttr.Name) ? prop.Name : colMapAttr.Name);
                                bool colIsModelOnly = (colAttrs.Length == 0 ? false : colMapAttr.ModelOnly);

                                /*allow model class properties different with datareader columns */
                                if (!columnNamesInQuery.Contains(colName)) continue;

                                if (colIsModelOnly)
                                    continue;

                                try
                                {
                                    if (reader[colName] is System.DBNull)
                                    {
                                        columnValue = default(T); //when Nullable type return null
                                    }
                                    else
                                    {
                                        //Check if Nullable type
                                        if (Nullable.GetUnderlyingType(prop.PropertyType) != null)
                                        {
                                            columnValue = Convert.ChangeType(reader[colName], Nullable.GetUnderlyingType(prop.PropertyType));
                                        }
                                        else
                                        {
                                            columnValue = Convert.ChangeType(reader[colName], prop.PropertyType);
                                        }
                                    }
                                    prop.SetValue(mapInstance, columnValue, null);
                                }
                                catch (Exception ex)
                                {
                                    string message = string.Format("error while read column name:[\"{0}\"]", colName);
                                    throw new TDataBaseException(message, ex);
                                }
                            }
                        }
                        mapList.Add(mapInstance);
                    }
                }, paramObj);

                return mapList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }
        }

        public DynamicList ExecuteDynamicList(string sql, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            try
            {
                var list = new DynamicList();

                Command.CommandType = commandType;
                Command.CommandText = sql;

                ExecuteReader(sql, (reader) =>
                {
                    while (reader.Read())
                    {
                        var eo = new ExpandoObject();
                        var eoDict = (IDictionary<string, object>)eo;
                        for (int i = 0; i < reader.FieldCount; i++)
                        {//fill value
                            eoDict.Add(reader.GetName(i), reader.GetValue(i));
                        }
                        dynamic obj = eo;

                        list.Add(obj);
                    }
                }, paramObj);

                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }
        }

        public void DoTransaction(Action<TDataBase> block, IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            DbTransaction trans = null;
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                trans = Connection.BeginTransaction(isolationLevel);
                Command.Transaction = trans;
                block(this);
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                Command.Transaction = null;
                Connection.Close();
            }
        }

        public void BulkCopyToSQL(DataTable SourceDataTable, string DestinationTableName)
        {
            SqlBulkCopy bcp = new SqlBulkCopy(Connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity);

            //mapping column, if Dstn table has identity column, do not select into DataTable
            foreach (DataColumn dc in SourceDataTable.Columns)
            {
                bcp.ColumnMappings.Add(dc.Caption, dc.Caption);
            }

            bcp.BulkCopyTimeout = 1500;
            bcp.BatchSize = 500000;
            bcp.DestinationTableName = DestinationTableName;
            bcp.WriteToServer(SourceDataTable);
        }

        public void BulkCopyToSQL(IDataReader reader, string DestinationTableName)
        {
            SqlBulkCopy bcp = new SqlBulkCopy(Connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity);

            bcp.BulkCopyTimeout = 1500;
            bcp.BatchSize = 500000;
            bcp.DestinationTableName = DestinationTableName;
            bcp.WriteToServer(reader);
        }
        #endregion

        #region DbParameter

        private void CreateDbParameters(object paramObj)
        {
            Command.Parameters.Clear();
            PropertyInfo[] propertyInfos = paramObj.GetType().GetProperties();
            foreach (PropertyInfo pi in propertyInfos)
            {
                try
                {
                    var param = Command.CreateParameter();
                    param.ParameterName = "@" + pi.Name;

                    var underlyinType = Nullable.GetUnderlyingType(pi.PropertyType);
                    if (underlyinType != null)
                    {//is nullable type
                        param.DbType = DBTypeMap[underlyinType];
                        param.Value = (pi.GetValue(paramObj, null) == null ? DBNull.Value : pi.GetValue(paramObj, null));
                    }
                    else
                    {
                        param.DbType = DBTypeMap[pi.PropertyType];
                        param.Value = pi.GetValue(paramObj, null);
                    }

                    Command.Parameters.Add(param);
                }
                catch
                {
                    throw new Exception("Error curred while create parameters from paramObj");
                }
            }
        }

        #region DBTypeMap
        private static Dictionary<Type, DbType> DBTypeMap = new Dictionary<Type, DbType>
        {
            { typeof(Boolean), DbType.Boolean },
            { typeof(Byte), DbType.Byte },
            { typeof(Char), DbType.String },
            { typeof(DateTime), DbType.DateTime },
            { typeof(Decimal), DbType.Decimal },
            { typeof(Double), DbType.Double },
            { typeof(Int16), DbType.Int16 },
            { typeof(Int32), DbType.Int32 },
            { typeof(Int64), DbType.Int64 },
            { typeof(SByte), DbType.SByte },
            { typeof(Single), DbType.Single},
            { typeof(String), DbType.String },
            { typeof(TimeSpan), DbType.Time },
            { typeof(UInt16), DbType.UInt16},
            { typeof(UInt32), DbType.UInt32 },
            { typeof(UInt64), DbType.UInt64 },
            { typeof(Guid), DbType.Guid },
            { typeof(Byte[]), DbType.Binary },
            { typeof(DateTimeOffset), DbType.DateTimeOffset },
            { typeof(Object), DbType.Object },
        };
        #endregion

        #endregion

        #region ConnectionString

        public string AddConnectionStringValue(string key, string value)
        {
            string newConnectionString = string.Empty;
            if (this.Connection.ConnectionString.EndsWith(";"))
            {
                newConnectionString =
                    this.Connection.ConnectionString + key + "=" + value + ";";
            }
            else
            {
                newConnectionString =
                    this.Connection.ConnectionString + ";" + key + "=" + value + ";";
            }

            return newConnectionString;
        }

        public string ChangeConnectionStringValue(string key, string newValue)
        {
            string newConnectionString = string.Empty;
            Regex rx = new Regex(@"(?<=\b" + key + @"\b\s*=\s*)([^;]+)", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            newConnectionString = rx.Replace(this.Connection.ConnectionString, newValue);
            return newConnectionString;
        }

        public string GetConnectionStringValue(string key)
        {
            Regex rx = new Regex(@"\b" + key + @"\b\s*=\s*([^;]+)", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            MatchCollection matches = rx.Matches(this.Connection.ConnectionString);
            string value = string.Empty;
            if (matches.Count == 1)
                value = matches[0].Groups[1].Value;
            else
                throw new Exception("Cannot find <" + key + "> in connection string");

            return value;
        }
        #endregion

        #region SqlServer BCP
        public void SqlBcpIn(string bcpExeFullPath, string inputFileName, string DestinationTableName, bool pauseWhenCompleted)
        {
            string server = GetConnectionStringValue("Data Source");
            string uid = GetConnectionStringValue("User ID");
            string pwd = GetConnectionStringValue("Password");

            string bcpbatch = "_tmpbatch_" + Path.GetRandomFileName() + ".bat";
            using (TextWriter tw2 = new StreamWriter(bcpbatch))
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("@echo off");
                sb.AppendFormat("\"{0}\" ", bcpExeFullPath);
                sb.AppendFormat("{0} in \"{1}\" ", DestinationTableName, inputFileName);
                sb.AppendFormat("-c -S\"{0}\" -U\"{1}\" -P\"{2}\" -b10000 -h\"TABLOCK\"", server, uid, pwd);
                sb.AppendLine();
                if (pauseWhenCompleted) sb.AppendLine("pause");
                tw2.WriteLine(sb.ToString());
            }

            var p = Process.Start("cmd", "/c " + bcpbatch);
            p.WaitForExit();
            File.Delete(bcpbatch);

        }

        public string SqlBcpTableOut(string bcpExeFullPath, string tableName, bool pauseWhenCompleted)
        {
            string output = tableName + "_" + DateTime.Now.ToString("yyyyMMdd_hhmmss") + ".dat";
            string server = GetConnectionStringValue("Data Source");
            string uid = GetConnectionStringValue("User ID");
            string pwd = GetConnectionStringValue("Password");

            string bcpbatch = "_tmpbatch_" + Path.GetRandomFileName() + ".bat";
            using (TextWriter tw2 = new StreamWriter(bcpbatch))
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("@echo off");
                sb.AppendFormat("\"{0}\" ", bcpExeFullPath);
                sb.AppendFormat("{0} out {1} ", tableName, output);
                sb.AppendFormat("-c -S{0} -U{1} -P{2}", server, uid, pwd);
                sb.AppendLine();
                if (pauseWhenCompleted) sb.AppendLine("pause");
                tw2.WriteLine(sb.ToString());
            }

            var p = Process.Start("cmd", "/c " + bcpbatch);
            p.WaitForExit();
            File.Delete(bcpbatch);

            return output;
        }

        public string SqlBcpQueryOut(string bcpExeFullPath, string sql, bool pauseWhenCompleted)
        {
            sql = sql.Replace(Environment.NewLine, " ");
            string output = string.Empty;

            Regex rx = new Regex(@"from\s([\w\.]+)", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            MatchCollection matches = rx.Matches(sql);

            if (matches.Count == 1)
            {
                output = matches[0].Groups[1].Value + "_" + DateTime.Now.ToString("yyyyMMdd_hhmmss") + ".dat";
            }
            else
            {
                output = "_bcpoutData_" + Path.GetRandomFileName() + ".dat";
            }

            string server = GetConnectionStringValue("Data Source");
            string uid = GetConnectionStringValue("User ID");
            string pwd = GetConnectionStringValue("Password");

            string bcpbatch = "_tmpbatch_" + Path.GetRandomFileName() + ".bat";
            using (TextWriter tw2 = new StreamWriter(bcpbatch))
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("@echo off");
                sb.AppendFormat("\"{0}\" ", bcpExeFullPath);
                sb.AppendFormat("\"{0}\" queryout {1} ", sql, output);
                sb.AppendFormat("-c -S{0} -U{1} -P{2}", server, uid, pwd);
                sb.AppendLine();
                if (pauseWhenCompleted) sb.AppendLine("pause");
                tw2.WriteLine(sb.ToString());
            }

            var p = Process.Start("cmd", "/c " + bcpbatch);
            p.WaitForExit();
            File.Delete(bcpbatch);

            return output;
        }
        #endregion

        public class DynamicList : List<dynamic>
        {
            public string ToJson()
            {
                if (this.Count == 0) return "";

                StringBuilder sb1 = new StringBuilder();
                sb1.Append("[");
                foreach(dynamic item in this)
                {
                    StringBuilder sb2 = new StringBuilder();
                    sb2.Append("{");

                    ExpandoObject eo = item;
                    var kvpList = eo.ToList();
                    foreach (var kvp in kvpList)
                    {
                        if (kvp.Value.GetType() == typeof(System.Byte[]))
                        {
                            byte[] bs = kvp.Value as byte[];
                            sb2.AppendFormat("\"{0}\":\"{1}\",", kvp.Key, FormatBytes(bs));
                        }
                        else
                        {
                            sb2.AppendFormat("\"{0}\":\"{1}\",", kvp.Key, kvp.Value);
                        }
                    }
                    string singleObjJSON = Regex.Replace(sb2.ToString(), @",$", "},");
                    sb1.Append(singleObjJSON);
                }

                string json = Regex.Replace(sb1.ToString(), @",$", "]");
                return json;
            }

            private string FormatBytes(byte[] val)
            {
                if (val == null) return string.Empty;

                StringBuilder sb = new StringBuilder();
                sb.Append("0x");
                sb.Append(BitConverter.ToString(val).Replace("-", ""));

                return sb.ToString();
            }
        }
    }
}
